﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports Utilerias
Imports WSFactory
Imports Npgsql
Imports MySql.Data.MySqlClient

Public Class SerieDAL
    Private cadConex As String = ""

    Public Sub New(ByVal pCadConex As String)
        Me.cadConex = pCadConex
    End Sub

#Region "FuncionesUtilizables"

    Public Function existeSerie(ByVal pSerie As String) As Boolean
        Dim numReg As Int16 = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM serie " & _
                                            "WHERE Serie = ?Serie;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("?Serie", pSerie)
                    myConn.Open()
                    numReg = Convert.ToInt16(myComm.ExecuteScalar())
                End Using
            End Using
            Return (numReg > 0)
        Catch ex As Exception
            Throw
        Finally
            numReg = Nothing
        End Try
    End Function

    Public Function registraSerie(ByVal pSerie As SerieBE) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    'myComm.CommandText = "INSERT INTO serie(Serie, FolioInicial, FolioSiguiente, Expedidos, NombreReporte, IdCertificado) " & _
                    '                        "VALUES (@Serie, @FolioInicial, @FolioSiguiente, @Expedidos, @NombreReporte, @IdCertificado);"
                    myComm.CommandText = "INSERT INTO serie(Serie, FolioInicial, FolioSiguiente, Expedidos, NombreReporte, IdCertificado, Estatus) " & _
                                            "SELECT @Serie, @FolioInicial, @FolioSiguiente, @Expedidos, @NombreReporte, IdCertificado, @estatus " & _
                                            "FROM certificados " & _
                                            "WHERE NumCertif = @NumCert;"
                    myComm.CommandType = System.Data.CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@Serie", pSerie.Serie)
                    myComm.Parameters.AddWithValue("@FolioInicial", pSerie.FolioInicial)
                    myComm.Parameters.AddWithValue("@FolioSiguiente", pSerie.FolioSiguiente)
                    myComm.Parameters.AddWithValue("@Expedidos", pSerie.Expedidos)
                    myComm.Parameters.AddWithValue("@NombreReporte", pSerie.NombreReporte)
                    If (pSerie.Estatus = SerieBE.EnumEstatus.Activo) Then
                        myComm.Parameters.AddWithValue("@estatus", "A")
                    Else
                        myComm.Parameters.AddWithValue("@estatus", "I")
                    End If
                    myComm.Parameters.AddWithValue("@NumCert", pSerie.NumSerieCertificado)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function cambiaStatus(ByVal pSerie As String, ByVal pSetActivo As Boolean) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE serie " & _
                                            "SET Estatus = @Status " & _
                                            "WHERE Serie = @serie;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    If (pSetActivo) Then
                        myComm.Parameters.AddWithValue("@Status", "A")
                    Else
                        myComm.Parameters.AddWithValue("@Status", "I")
                    End If
                    myComm.Parameters.AddWithValue("@serie", pSerie)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Function AsignaReporte(ByVal Serie As String, ByVal strNombreReporte As String) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE serie " & _
                                            "SET NombreReporte = @reporte " & _
                                            "WHERE Serie = @serie;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@reporte", strNombreReporte)
                    myComm.Parameters.AddWithValue("@serie", Serie)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function obtenerFolioSiguiente(ByVal pSerie As String) As Integer
        Dim folio As Integer = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "spObtenerFolioPorSerie"
                    myComm.CommandType = CommandType.StoredProcedure
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("?serieAct", pSerie)
                    myComm.Parameters.Add(New MySqlParameter("?folio", MySqlDbType.Int32))
                    myComm.Parameters("?folio").Direction = ParameterDirection.Output
                    myConn.Open()
                    myComm.ExecuteNonQuery()
                    folio = Convert.ToInt32(myComm.Parameters("?folio").Value)
                    Return folio
                End Using
            End Using
        Catch ex As Exception
            Throw
        Finally
            folio = Nothing
        End Try
    End Function

    Public Function getSerie(ByVal Serie As String) As SerieBE
        Dim tmpSerie As SerieBE
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdSerie, Serie, FolioInicial, FolioSiguiente, Expedidos, NombreReporte, NumCertif, serie.Estatus " & _
                                            "FROM serie INNER JOIN Certificados ON Certificados.IdCertificado = serie.IdCertificado " & _
                                            "WHERE Serie = @ConSerie;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@ConSerie", Serie)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpSerie = SerieFact.Build(myDR)
                        Else
                            tmpSerie = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpSerie

        Catch ex As Exception
            Throw
        Finally
            tmpSerie = Nothing
        End Try
    End Function

    Public Function getListadoSerie(ByVal tipoEstatus As Enumeraciones.estatusParaBusquedas) As List(Of SerieBE)
        Dim SerieLs As List(Of SerieBE) = New List(Of SerieBE)
        Dim strQryWhere As String = ""
        If (tipoEstatus <> Enumeraciones.estatusParaBusquedas.Todos) Then
            If (tipoEstatus = Enumeraciones.estatusParaBusquedas.Activos) Then
                strQryWhere = " WHERE Estatus = 'A'"
            Else
                strQryWhere = " WHERE Estatus = 'I'"
            End If
        End If
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdSerie, Serie, FolioInicial, FolioSiguiente, Expedidos, NombreReporte, NumCertif, serie.Estatus " & _
                                           "FROM serie INNER JOIN Certificados ON Certificados.IdCertificado = serie.IdCertificado " & _
                                           strQryWhere
                    myComm.CommandType = CommandType.Text
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            Dim Serie As SerieBE = SerieFact.Build(myDR)
                            If (Not Serie Is Nothing) Then
                                SerieLs.Add(Serie)
                            End If
                            Serie = Nothing
                        End While
                    End Using
                End Using
            End Using
            Return SerieLs
        Catch ex As Exception
            Throw
        Finally
            SerieLs = Nothing
            strQryWhere = Nothing
        End Try
    End Function

    Function cambiaCertificado(ByVal Serie As String, ByVal numCertif As String) As Boolean
        Dim idCertif As Object = Nothing
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdCertificado " & _
                                            "FROM certificados " & _
                                            "WHERE NumCertif = @numCertif;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@numCertif", numCertif)
                    myConn.Open()
                    idCertif = myComm.ExecuteScalar()
                    If (idCertif Is Nothing) Then
                        Return False
                    End If

                    myComm.CommandText = "UPDATE serie " & _
                                            "SET IdCertificado = @idCertif " & _
                                            "WHERE Serie = @serie;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@idCertif", Convert.ToInt32(idCertif))
                    myComm.Parameters.AddWithValue("@serie", Serie)
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        Finally
            idCertif = Nothing
        End Try
    End Function

#End Region

End Class
